#delimit;
clear;
capture log close;
set more off;

local in "/data";

local out "/data";

use "`in'/rollup_merged_97_14.dta", clear;
sort pid year;

label var t "t=1 if data comes from 2010/06 (new data) and 0 if from 2010/12 (old data)";
/*Drop old 2010 data*/
drop if t==0;

keep pid year tr_am dtopen dtclo loantype;
duplicates drop;

local var="1997 1999 2001 2003 2004 2007 2008 2010 2012 2014";
foreach i of local var {;
gen score`i't=tr_am if year==`i';
bys pid: egen score`i'=max(score`i't);
drop score`i't;
};

/*
#delimit;
drop if loantype=="";
drop if dtopen==.;
drop if dtopen==0;
drop if dtopen>dtclo & dtopen~=. & dtclo~=. & dtclo~=0;
drop dtclo;
*/
sort pid year;
gen dtopen_temp=string(dtopen)+string(28);
gen dtopen_st=date(dtopen_temp,"YMD");
format dtopen_st %td;
drop dtopen_temp;

local var="1997 1999 2001 2003 2007 2010 2012 2014";
foreach i of local var {;
gen tu_`i'=mdy(6,28,`i');
};
local var="2004 2008";
foreach i of local var {;
gen tu_`i'=mdy(12,28,`i');
};

gen year_n=1 if year==1997;
replace year_n=2 if year==1999;
replace year_n=3 if year==2001;
replace year_n=4 if year==2003;
replace year_n=5 if year==2004;
replace year_n=6 if year==2007;
replace year_n=7 if year==2008;
replace year_n=8 if year==2010;
replace year_n=9 if year==2012;
replace year_n=10 if year==2014;


gen month=6 if (year_n>=1 & year_n<=4) | year_n==6 | (year_n>=8 & year_n<=10);
replace month=12 if year_n==5 | year_n==7;
gen tu_year=mdy(month,28,year);
format tu_year %td;
drop month;

sort pid year year_n;
gen score=.;
replace score=score1997 if dtopen_st>=tu_1997 & dtopen_st<tu_1999;
replace score=score1999 if dtopen_st>=tu_1999 & dtopen_st<tu_2001;
replace score=score2001 if dtopen_st>=tu_2001 & dtopen_st<tu_2003;
replace score=score2003 if dtopen_st>=tu_2003 & dtopen_st<tu_2004;
replace score=score2004 if dtopen_st>=tu_2004 & dtopen_st<tu_2007;
replace score=score2007 if dtopen_st>=tu_2007 & dtopen_st<tu_2008;
replace score=score2008 if dtopen_st>=tu_2008 & dtopen_st<tu_2010;
replace score=score2010 if dtopen_st>=tu_2010 & dtopen_st<tu_2012;
replace score=score2012 if dtopen_st>=tu_2012 & dtopen_st<tu_2014;
replace score=score2014 if dtopen_st==tu_2014;

keep if score~=.;
keep pid score loantype;

gen loantype2="COLL" if loantype=="INST" | loantype=="SECG";

tab loantype;
#delimit;
twoway kdensity score if loantype=="MORT" || kdensity score if loantype=="AUTO" || kdensity score if loantype=="CCAR" || kdensity score if loantype=="INST" || kdensity score if loantype=="SECG" || kdensity score if loantype=="STUD";

twoway kdensity score if loantype=="MORT" || kdensity score if loantype=="AUTO" || kdensity score if loantype=="CCAR" || kdensity score if loantype2=="COLL" || kdensity score if loantype=="STUD";

gen loan=loantype;
replace loan="COLL" if loantype2=="COLL";

gen subprime=0;
gen deepsub=0;
replace subprime=1 if score<620 & score>0;
replace deepsub=1 if score<526 & score>0;

/*
Figure 2.A;
*/
preserve;
kdensity score if loantype=="MORT" & score>=270 & score<=900, generate(x_mort y_mort) nograph;
kdensity score if loantype=="AUTO" & score>=270 & score<=900, generate(x_auto y_auto) nograph;
kdensity score if loantype=="CCAR" & score>=270 & score<=900, generate(x_ccar y_ccar) nograph;
kdensity score if loantype=="SECG" & score>=270 & score<=900, generate(x_secg y_secg) nograph;
kdensity score if loantype=="INST" & score>=270 & score<=900, generate(x_inst y_inst) nograph;
drop if x_mort==.;
export excel x_mort y_mort x_ccar y_ccar x_auto y_auto x_secg y_secg x_inst y_inst using "`out'/Kdensity_5debts.xlsx", firstrow(variables) replace;
restore;

